Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Runtime.InteropServices
Imports System.Configuration
Imports System.Text
Public Class SearchAudit
    Private sSQL As String
    Private sReturn As String
    Public DBConnectString As String
    Public strError As String
    Public Function GetEvents() As DataSet

        Dim sSQL As String
        Dim ds As New DataSet
        Dim Rst As New DataSet
        Dim strTDError As String = ""
        sSQL = "SELECT Event_ID, EventDescription FROM AUDIT_EVENTS"
        sSQL = sSQL & " order by DisplayOrder "
        Try
            GetDataset(DBConnectString, sSQL, "auditevent", strTDError, Rst)
            ds = Rst
            If ds.Tables(0).Rows.Count > 0 Then
                GetEvents = ds

            End If
            If Len(strTDError) > 0 Then
                LogErrorEvent(strTDError)
            Else
                strError = "No data found"
                LogErrorEvent(strError)
            End If

        Catch ex As SqlException
            Dim strErrEx As String = ""
            Dim strTDErr As String = ""
            Dim i As Integer = 0

            For i = 0 To ex.Errors.Count - 1
                strTDErr = ex.Errors(i).Message.ToString()
            Next i
            ''/**use this code to debug**//

            LogErrorEvent(strTDErr)


        End Try

        Return ds
    End Function

    Public Function GetAuditRecords(ByVal aud_sess_vid As String, _
    ByVal aud_sess_cid As String, _
    ByVal aud_sess_uid As String, _
    ByVal aud_aff_vid As String, _
    ByVal aud_aff_cid As String, _
    ByVal aud_aff_uid As String, _
    ByVal aud_et As String, _
    ByVal aud_sip As String, _
    ByVal aud_fd As String, _
    ByVal aud_ft As String, _
    ByVal aud_td As String, _
    ByVal aud_tt As String, _
    ByVal aud_do As String) As DataSet

        Dim sSQL As String
        Dim strTDError As String = ""
        Dim Rst2 As New DataSet
        'Dim ds As New DataSet
       
        '#####  NEW CODE
        '-----  Building SQL Statement based on search parameters.
        sSQL = ""
        sSQL = sSQL & "select * from (select "
        sSQL = sSQL & "     AUDITS.Audit_ID, "
        sSQL = sSQL & "     AUDIT_EVENTS.EventDescription, "
        sSQL = sSQL & "     AUDITS.DateTime, "
        sSQL = sSQL & "     AUDITS.SESSION_User_ID, "
        sSQL = sSQL & "     AUDITS.SESSION_Company_ID, "
        sSQL = sSQL & "     AUDITS.SESSION_Viaklix_ID, "
        sSQL = sSQL & "     AUDITS.AFFECTING_User_ID, "
        sSQL = sSQL & "     AUDITS.AFFECTING_Company_ID, "
        sSQL = sSQL & "     AUDITS.AFFECTING_Viaklix_ID, "
        sSQL = sSQL & "    AUDITED_INFO.Data "
        sSQL = sSQL & "from "
        sSQL = sSQL & "    AUDITS , AUDIT_EVENTS, AUDITED_INFO "
        sSQL = sSQL & "Where "
        sSQL = sSQL & "    AUDITS.Event_ID = AUDIT_EVENTS.Event_ID "
        sSQL = sSQL & "    and AUDITS.Audit_ID = AUDITED_INFO.Audit_ID(+) "
        sSQL = sSQL & "    and AUDITED_INFO.Audit_ID is NULL "
        If Len(aud_sip) > 0 Then
            sSQL = sSQL & "     and AUDITED_INFO.Data like '%" & aud_sip & "%' "
        End If
        If Len(aud_sess_vid) > 0 Then
            sSQL = sSQL & "     and AUDITS.SESSION_Viaklix_ID = '" & Replace(aud_sess_vid, "'", "''") & "' "
        End If
        If Len(aud_sess_cid) > 0 Then
            sSQL = sSQL & "     and AUDITS.SESSION_Company_ID =" & CInt(aud_sess_cid) & " "
        End If
        If Len(aud_sess_uid) > 0 Then
            sSQL = sSQL & "     and AUDITS.SESSION_User_ID = '" & Replace(aud_sess_uid, "'", "''") & "' "
        End If
        If Len(aud_aff_vid) > 0 Then
            sSQL = sSQL & "     and AUDITS.AFFECTING_Viaklix_ID = '" & Replace(aud_aff_vid, "'", "''") & "' "
        End If
        If Len(aud_aff_cid) > 0 Then
            sSQL = sSQL & "     and AUDITS.AFFECTING_Company_ID =" & CInt(aud_aff_cid) & " "
        End If
        If Len(aud_aff_uid) > 0 Then
            sSQL = sSQL & "     and AUDITS.AFFECTING_User_ID = '" & Replace(aud_aff_uid, "'", "''") & "' "
        End If
        If CInt(aud_et) > 0 Then
            sSQL = sSQL & "     and AUDIT_EVENTS.Event_ID = " & aud_et & " "
        End If
        If IsDate(aud_fd) Then
            If Len(aud_ft) > 0 Then
                sSQL = sSQL & "     and AUDITS.DateTime >= to_date('" & FormatDateTime(aud_fd, vbShortDate)
                sSQL = sSQL & " " & aud_ft & "', 'MM/DD/YY HH:MI PM') "
            Else
                sSQL = sSQL & "     and AUDITS.DateTime >= to_date('" & FormatDateTime(aud_fd, vbShortDate)
                sSQL = sSQL & "', 'MM/DD/YY') "
            End If
        End If
        If IsDate(aud_td) Then
            If Len(aud_tt) > 0 Then
                If UCase(aud_tt) = "11:59 PM" Then
                    sSQL = sSQL & "     and AUDITS.DateTime < to_date('" & FormatDateTime(DateAdd("d", 1, aud_td), vbShortDate)
                    sSQL = sSQL & " " & "12:00 AM" & "', 'MM/DD/YY HH:MI PM') "
                ElseIf UCase(aud_tt) = "12:59 AM" Then
                    sSQL = sSQL & "     and AUDITS.DateTime < to_date('" & FormatDateTime(aud_td, vbShortDate)
                    sSQL = sSQL & " " & "1:00 AM" & "', 'MM/DD/YY HH:MI PM') "
                ElseIf UCase(aud_tt) = "12:59 PM" Then
                    sSQL = sSQL & "     and AUDITS.DateTime < to_date('" & FormatDateTime(aud_td, vbShortDate)
                    sSQL = sSQL & " " & "1:00 PM" & "', 'MM/DD/YY HH:MI PM') "
                Else
                    sSQL = sSQL & "     and AUDITS.DateTime < to_date('" & FormatDateTime(aud_td, vbShortDate)
                    sSQL = sSQL & " " & CStr(CInt(Left(aud_tt, 2)) + 1) & ":00 " & Right(aud_tt, 2) & "', 'MM/DD/YY HH:MI PM') "
                End If
            Else
                sSQL = sSQL & "     and AUDITS.DateTime < to_date('" & DateAdd("d", 1, FormatDateTime(aud_td, vbShortDate))
                sSQL = sSQL & "', 'MM/DD/YY') "
            End If
        End If
        sSQL = sSQL & "Union "
        sSQL = sSQL & "  select "
        sSQL = sSQL & "     AUDITS.Audit_ID, "
        sSQL = sSQL & "     AUDIT_EVENTS.EventDescription, "
        sSQL = sSQL & "     AUDITS.DateTime, "
        sSQL = sSQL & "     AUDITS.SESSION_User_ID, "
        sSQL = sSQL & "     AUDITS.SESSION_Company_ID, "
        sSQL = sSQL & "     AUDITS.SESSION_Viaklix_ID, "
        sSQL = sSQL & "     AUDITS.AFFECTING_User_ID, "
        sSQL = sSQL & "     AUDITS.AFFECTING_Company_ID, "
        sSQL = sSQL & "     AUDITS.AFFECTING_Viaklix_ID, "
        sSQL = sSQL & "    AUDITED_INFO.Data From "
        sSQL = sSQL & "    AUDITS , AUDIT_EVENTS, AUDITED_INFO "
        sSQL = sSQL & "Where "
        sSQL = sSQL & "    AUDITS.Event_ID = AUDIT_EVENTS.Event_ID "
        sSQL = sSQL & "    and AUDITS.Audit_ID = AUDITED_INFO.Audit_ID "
        sSQL = sSQL & "    and AUDITED_INFO.Audit_Field_ID = 70 "
        If Len(aud_sip) > 0 Then
            sSQL = sSQL & "     and AUDITED_INFO.Data like '%" & aud_sip & "%' "
        End If
        If Len(aud_sess_vid) > 0 Then
            sSQL = sSQL & "     and AUDITS.SESSION_Viaklix_ID = '" & Replace(aud_sess_vid, "'", "''") & "' "
        End If
        If Len(aud_sess_cid) > 0 Then
            sSQL = sSQL & "     and AUDITS.SESSION_Company_ID =" & CInt(aud_sess_cid) & " "
        End If
        If Len(aud_sess_uid) > 0 Then
            sSQL = sSQL & "     and AUDITS.SESSION_User_ID = '" & Replace(aud_sess_uid, "'", "''") & "' "
        End If
        If Len(aud_aff_vid) > 0 Then
            sSQL = sSQL & "     and AUDITS.AFFECTING_Viaklix_ID = '" & Replace(aud_aff_vid, "'", "''") & "' "
        End If
        If Len(aud_aff_cid) > 0 Then
            sSQL = sSQL & "     and AUDITS.AFFECTING_Company_ID =" & CInt(aud_aff_cid) & " "
        End If
        If Len(aud_aff_uid) > 0 Then
            sSQL = sSQL & "     and AUDITS.AFFECTING_User_ID = '" & Replace(aud_aff_uid, "'", "''") & "' "
        End If
        If CInt(aud_et) > 0 Then
            sSQL = sSQL & "     and AUDIT_EVENTS.Event_ID = " & aud_et & " "
        End If
        If IsDate(aud_fd) Then
            If Len(aud_ft) > 0 Then
                sSQL = sSQL & "     and AUDITS.DateTime >= to_date('" & FormatDateTime(aud_fd, vbShortDate)
                sSQL = sSQL & " " & aud_ft & "', 'MM/DD/YY HH:MI PM') "
            Else
                sSQL = sSQL & "     and AUDITS.DateTime >= to_date('" & FormatDateTime(aud_fd, vbShortDate)
                sSQL = sSQL & "', 'MM/DD/YY') "
            End If
        End If
        If IsDate(aud_td) Then
            If Len(aud_tt) > 0 Then
                If UCase(aud_tt) = "11:59 PM" Then
                    sSQL = sSQL & "     and AUDITS.DateTime < to_date('" & FormatDateTime(DateAdd("d", 1, aud_td), vbShortDate)
                    sSQL = sSQL & " " & "12:00 AM" & "', 'MM/DD/YY HH:MI PM') "
                ElseIf UCase(aud_tt) = "12:59 AM" Then
                    sSQL = sSQL & "     and AUDITS.DateTime < to_date('" & FormatDateTime(aud_td, vbShortDate)
                    sSQL = sSQL & " " & "1:00 AM" & "', 'MM/DD/YY HH:MI PM') "
                ElseIf UCase(aud_tt) = "12:59 PM" Then
                    sSQL = sSQL & "     and AUDITS.DateTime < to_date('" & FormatDateTime(aud_td, vbShortDate)
                    sSQL = sSQL & " " & "1:00 PM" & "', 'MM/DD/YY HH:MI PM') "
                Else
                    sSQL = sSQL & "     and AUDITS.DateTime < to_date('" & FormatDateTime(aud_td, vbShortDate)
                    sSQL = sSQL & " " & CStr(CInt(Left(aud_tt, 2)) + 1) & ":00 " & Right(aud_tt, 2) & "', 'MM/DD/YY HH:MI PM') "
                End If
            Else
                sSQL = sSQL & "     and AUDITS.DateTime < to_date('" & DateAdd("d", 1, FormatDateTime(aud_td, vbShortDate))
                sSQL = sSQL & "', 'MM/DD/YY') "
            End If
        End If
        'sSQL = sSQL & ") order by 3 desc"
        If aud_do = "Newest First" Then
            sSQL = sSQL & ") order by 3 desc"
        ElseIf aud_do = "Oldest First" Then
            sSQL = sSQL & ") order by 3"
        End If
        '#####  END NEW CODE
        '-----  Building SQL Statement based on search parameters.
        'sSQL = ""
        'sSQL = sSQL & "select "
        'sSQL = sSQL & " AUDITS.Audit_ID, "
        'sSQL = sSQL & " AUDIT_EVENTS.EventDescription, "
        'sSQL = sSQL & " AUDITS.DateTime, "
        'sSQL = sSQL & " AUDITS.SESSION_User_ID, "
        'sSQL = sSQL & " AUDITS.SESSION_Company_ID, "
        'sSQL = sSQL & " AUDITS.SESSION_Viaklix_ID, "
        'sSQL = sSQL & " AUDITS.AFFECTING_User_ID, "
        'sSQL = sSQL & " AUDITS.AFFECTING_Company_ID, "
        'sSQL = sSQL & " AUDITS.AFFECTING_Viaklix_ID "
        'sSQL = sSQL & " from "
        'sSQL = sSQL & " AUDITS, AUDIT_EVENTS "
        'sSQL = sSQL & "where "
        'sSQL = sSQL & "     AUDITS.Event_ID = AUDIT_EVENTS.Event_ID "
        'If Len(aud_sess_vid) > 0 Then
        '    sSQL = sSQL & "     and AUDITS.SESSION_Viaklix_ID = '" & Replace(aud_sess_vid, "'", "''") & "' "
        'End If
        'If Len(aud_sess_cid) > 0 Then
        '    sSQL = sSQL & "     and AUDITS.SESSION_Company_ID =" & CInt(aud_sess_cid) & " "
        'End If
        'If Len(aud_sess_uid) > 0 Then
        '    sSQL = sSQL & "     and AUDITS.SESSION_User_ID = '" & Replace(aud_sess_uid, "'", "''") & "' "
        'End If
        'If Len(aud_aff_vid) > 0 Then
        '    sSQL = sSQL & "     and AUDITS.AFFECTING_Viaklix_ID = '" & Replace(aud_aff_vid, "'", "''") & "' "
        'End If
        'If Len(aud_aff_cid) > 0 Then
        '    sSQL = sSQL & "     and AUDITS.AFFECTING_Company_ID =" & CInt(aud_aff_cid) & " "
        'End If
        'If Len(aud_aff_uid) > 0 Then
        '    sSQL = sSQL & "     and AUDITS.AFFECTING_User_ID = '" & Replace(aud_aff_uid, "'", "''") & "' "
        'End If
        'If CInt(aud_et) > 0 Then
        '    sSQL = sSQL & "     and AUDIT_EVENTS.Event_ID = " & aud_et & " "
        'End If
        'If IsDate(aud_fd) Then
        '    If Len(aud_ft) > 0 Then
        '        sSQL = sSQL & "     and AUDITS.DateTime > to_date('" & FormatDateTime(aud_fd, vbShortDate)
        '        sSQL = sSQL & " " & aud_ft & "', 'MM/DD/YY HH:MI PM') "
        '    Else
        '        sSQL = sSQL & "     and AUDITS.DateTime > to_date('" & FormatDateTime(aud_fd, vbShortDate)
        '        sSQL = sSQL & "', 'MM/DD/YY') "
        '    End If
        'End If
        'If IsDate(aud_td) Then
        '    If Len(aud_tt) > 0 Then
        '        sSQL = sSQL & "     and AUDITS.DateTime < to_date('" & FormatDateTime(aud_td, vbShortDate)
        '        sSQL = sSQL & " " & aud_tt & "', 'MM/DD/YY HH:MI PM') "
        '    Else
        '        sSQL = sSQL & "     and AUDITS.DateTime < to_date('" & FormatDateTime(aud_td, vbShortDate)
        '        sSQL = sSQL & "', 'MM/DD/YY') "
        '    End If
        'End If
        'sSQL = sSQL & "order by "
        'If aud_do = "Newest First" Then
        '    sSQL = sSQL & "AUDITS.DateTime desc"
        'ElseIf aud_do = "Oldest First" Then
        '    sSQL = sSQL & "AUDITS.DateTime"
        'End If
        '-----  Done building SQL Statement based on search parameters
        Try
            GetDataset(DBConnectString, sSQL, "auditevent", strTDError, Rst2)

            If Rst2.Tables(0).Rows.Count > 0 Then
                'GetAuditRecords = Rst2
            Else
                strError = "No data found"
                LogErrorEvent(strError)
            End If

        Catch ex As SqlException
            Dim strErrEx As String = ""
            Dim strTDErr As String = ""
            Dim i As Integer = 0

            For i = 0 To ex.Errors.Count - 1
                strTDErr = ex.Errors(i).Message.ToString()
            Next i
            ''/**use this code to debug**//

            LogErrorEvent(strTDErr)


        End Try
        Return Rst2

    End Function

    Public Function GetAuditDetails(ByVal aud_aid As Long) As DataSet

        'Dim Conn As SqlConnection
        Dim sSQL As String
        Dim Rst3 As New DataSet
        Dim strTDError As String = ""
        sSQL = ""
        sSQL = sSQL & "select "
        sSQL = sSQL & "     AUDIT_EVENTS.EventDescription, "
        sSQL = sSQL & "     AUDITS.DateTime, "
        sSQL = sSQL & "     AUDITS.SESSION_User_ID, "
        sSQL = sSQL & "     AUDITS.SESSION_Company_ID, "
        sSQL = sSQL & "     AUDITS.SESSION_Viaklix_ID, "
        sSQL = sSQL & "     AUDITS.AFFECTING_User_ID, "
        sSQL = sSQL & "     AUDITS.AFFECTING_Company_ID, "
        sSQL = sSQL & "     AUDITS.AFFECTING_Viaklix_ID "
        sSQL = sSQL & "from "
        sSQL = sSQL & "     AUDITS, AUDIT_EVENTS "
        sSQL = sSQL & "where "
        sSQL = sSQL & "     AUDITS.Event_ID = AUDIT_EVENTS.Event_ID and "
        sSQL = sSQL & "     AUDITS.Audit_ID = " & aud_aid
        ' Rst3 = New DataSet
        Try
            GetDataset(DBConnectString, sSQL, "auditDetails", strTDError, Rst3)
            If Rst3.Tables(0).Rows.Count > 0 Then
                GetAuditDetails = Rst3
            Else
                strError = "No data found"
                LogErrorEvent(strError)
            End If

        Catch ex As SqlException
            Dim strErrEx As String = ""
            Dim strTDErr As String = ""
            Dim i As Integer = 0

            For i = 0 To ex.Errors.Count - 1
                strTDErr = ex.Errors(i).Message.ToString()
            Next i
            ''/**use this code to debug**//

            LogErrorEvent(strTDErr)


        End Try
       
          
        Return Rst3
    End Function

    Public Function GetAuditAdditionalInfo(ByVal aud_aid As Long) As DataSet

        Dim sSQL As String
        Dim Rst4 As New DataSet
        Dim strTDError As String = ""
        sSQL = ""
        sSQL = sSQL & "select "
        sSQL = sSQL & "     AUDIT_FIELDS.Description, "
        sSQL = sSQL & "     AUDITED_INFO.Data "
        sSQL = sSQL & "from "
        sSQL = sSQL & "     AUDITED_INFO, AUDIT_FIELDS "
        sSQL = sSQL & "where "
        sSQL = sSQL & "     AUDITED_INFO.Audit_Field_ID = AUDIT_FIELDS.Audit_Field_ID and "
        sSQL = sSQL & "     AUDITED_INFO.Audit_ID = " & aud_aid
        Try
            GetDataset(DBConnectString, sSQL, "auditInfo", strTDError, Rst4)
            If Rst4.Tables(0).Rows.Count > 0 Then
                'GetAuditAdditionalInfo = Rst4
            Else
                strError = "No data found"
                LogErrorEvent(strError)
            End If

        Catch ex As SqlException
            Dim strErrEx As String = ""
            Dim strTDErr As String = ""
            Dim i As Integer = 0

            For i = 0 To ex.Errors.Count - 1
                strTDErr = ex.Errors(i).Message.ToString()
            Next i
            ''/**use this code to debug**//

            LogErrorEvent(strTDErr)


        End Try
        Return Rst4
    End Function

    ' checks that the date is in the format mm/dd/yy or atleast mm/dd so we can assume the current year
    Public Function ValidateDate(ByVal d As String) As Boolean
        Dim pos As Integer
        Dim buffer As String
        Dim mon As String
        Dim dt As String
        Dim yr As String
        Dim data As New Date
        data = Now()
        ' innocent until proven guilty
        ValidateDate = True

        If d <> vbNullString Then
            buffer = d

            pos = InStr(buffer, "/")
            If pos <> 0 Then
                mon = Left$(buffer, pos - 1)
                If Len(mon) < 3 And IsNumeric(mon) Then
                    If CInt(mon) > 12 Or CInt(mon) < 0 Then
                        ValidateDate = False
                        Exit Function
                    End If
                Else
                    ValidateDate = False
                    Exit Function
                End If
            Else
                ValidateDate = False
                Exit Function
            End If
            buffer = Right$(d, Len(d) - pos)
            pos = InStr(buffer, "/")
            If pos <> 0 Then
                dt = Left$(buffer, pos - 1)
                If Len(dt) < 3 And IsNumeric(dt) Then
                    If CInt(dt) > 31 Or CInt(dt) < 0 Then
                        ValidateDate = False
                        Exit Function
                    End If
                Else
                    ValidateDate = False
                    Exit Function
                End If

                yr = Right$(buffer, Len(buffer) - pos)
                If Len(yr) < 5 And IsNumeric(yr) Then
                    If CInt(yr) < 0 Then
                        ValidateDate = False
                        Exit Function
                    End If
                Else
                    ValidateDate = False
                    Exit Function
                End If
            Else
                dt = buffer
                If Len(dt) < 3 And IsNumeric(dt) Then
                    If CInt(dt) > 31 Or CInt(dt) < 0 Then
                        ValidateDate = False
                        Exit Function
                    End If
                Else
                    ValidateDate = False
                    Exit Function
                End If

                yr = Right$(data, 2)
            End If


            If Not IsDate(d) Then ValidateDate = False
            LogErrorEvent(".ValidateDate")
        End If



        'LogErrorEvent(".ValidateDate")
    End Function
    Public Function LogErrorEvent(ByVal sOrigin As String) As String
        Dim sErrMsg As String
        sErrMsg = ""
        sErrMsg = sErrMsg & "viaAudit.SearchAudit" & sOrigin & vbCrLf
        sErrMsg = sErrMsg & "Source: " & Err.Source & vbCrLf
        sErrMsg = sErrMsg & "Number: " & Err.Number & vbCrLf
        sErrMsg = sErrMsg & "Description: " & Err.Description
        'App.LogEvent(sErrMsg, vbLogEventTypeError)
        'Debug.Print(sErrMsg)
        Return sErrMsg
    End Function

End Class
